package excelFileReader;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.text.ParsePosition;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * @author Idan
 *This class is used to perform ETL on Polish reports
 */
public class ExcelReaderPolish
{

	private static Connection conn = null;
	private static ArrayList<Object> row = new ArrayList<Object>();
	public void processOneSheet(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		// rId2 found by processing the Workbook
		// Seems to either be rId# or rSheet#
		InputStream sheet2 = r.getSheet("rId2");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	public void processAllSheets(String filename) throws Exception 
	{
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		
		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while(sheets.hasNext())
		{
			System.out.println("Processing new sheet:\n");
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			System.out.println("");
		}
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException 
	{
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	/** 
	 * See org.xml.sax.helpers.DefaultHandler javadocs 
	 */
	public static class SheetHandler extends DefaultHandler
	{
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		
		private SheetHandler(SharedStringsTable sst) 
		{
			this.sst = sst;
		}
		
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException 
				{
			// c => cell
			if(name.equals("c")) {
				// Print the cell reference
				System.out.print(attributes.getValue("r") + " - ");
				
				if ( attributes.getValue("r").startsWith("J") ) // Test if we reached the last column
				{
					//row.add("null"); // Empty comment
				// Time to dump the line and start a new one
						System.out.print("NEW LINE!\n");
					
				}			
				
				
				// Figure out if the value is an index in the SST
				String cellType = attributes.getValue("t");
				if(cellType != null && cellType.equals("s"))
				{	
					nextIsString = true;
					
				} else {
					nextIsString = false;
				}
			}
			// Clear contents cache
			lastContents = "";
		}
		
		public void endElement(String uri, String localName, String name)
				throws SAXException
				{
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if(nextIsString) {
				int idx = Integer.parseInt(lastContents);			
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
				
				
            nextIsString = false;
			}

			// v => contents of a cell
			// Output after we've seen the string contents
			if(name.equals("v"))
			{
				row.add(lastContents); // Add value to row
				System.out.println("added " + lastContents);
			
			}
		}

		

		public void characters(char[] ch, int start, int length)
				throws SAXException
				{
			lastContents += new String(ch, start, length);
		}
	}
	

	
	
	public void test() throws SQLException
	{
		Statement stmt = null;
		ResultSet rs = null;
	    stmt = conn.createStatement();
	    rs = stmt.executeQuery("SELECT * FROM status");
	    
	    while(rs.next())
	    {
	    	
	    	System.out.println(rs.getString(1));
	    	System.out.println(rs.getString(2));

	    
	    }
	    
	    if (rs != null) {
	        try {
	            rs.close();
	        } catch (SQLException sqlEx) { } // ignore
	        rs = null;
	    }
	    if (stmt != null) {
	        try {
	            stmt.close();
	        } catch (SQLException sqlEx) { } // ignore
	        stmt = null;
	    }
	}
	
	public static void main(String[] args) throws Exception 
	{
		ExcelReaderPolish howto = new ExcelReaderPolish();
		
		
		
		try
		{
		    conn = DriverManager.getConnection("jdbc:mysql://localhost/hanitdb?" +
		                                   "user=root&password=&useUnicode=true&characterEncoding=UTF-8");
		    
		    
		 
		   howto.processAllSheets("polish2014.xlsx");
		
	        
	       
				
			//	row.clear(); // clear array

		}
		 catch (SQLException ex)
		 {
			    // handle any errors
			    System.out.println("SQLException: " + ex.getMessage());
			    System.out.println("SQLState: " + ex.getSQLState());
			    System.out.println("VendorError: " + ex.getErrorCode());
			}
		 sendToDB(row); // commit to DB
		
	}

	private static void sendToDB(ArrayList<Object> row) throws SQLException 
	{
		Statement stmt = conn.createStatement();
		Statement lastIDstmt = conn.createStatement();
		String lastInsertID;
		Object tempList;
		ArrayList<Object> listOfOrders= new ArrayList<Object>();
		
		int i=0;
		int j;
		while (i<(row.size()))
		{
			
			// decide row status
			if (((String)row.get(i)).contentEquals("*")) {
			}
			else {
			}
			
			Date beginDate = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble((String)row.get(i+10)));
			Date originalEndDate = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble((String)row.get(i+13)));
			Date endDate = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble((String)row.get(i+17)));

			java.sql.Date beginDateDB = new java.sql.Date(beginDate.getTime());
			java.sql.Date originalEndDateDB = new java.sql.Date(originalEndDate.getTime());
			java.sql.Date endDateDB = new java.sql.Date(endDate.getTime());
			
		
			row.set(i+10, beginDateDB);
			row.set(i+13, originalEndDateDB);
			row.set(i+17, endDateDB);
			
			// Is this a repolish?
			if (!(row.get(i+12).equals("0")))
				row.set(i+12, "1");
			
			// remove " ' " and " " " from the comment
			row.set(i+9, ((String)(row.get(i+9))).replace("'", "" ));
			row.set(i+9, ((String)(row.get(i+9))).replace('"', ' ' ));
			// insert row into polish
		
			stmt.executeUpdate("INSERT INTO polish(jarID,beginDate,originalEndDate,endDate,topline,comment) VALUES('" + row.get(i+1) + "','" + row.get(i+10) +"','" + row.get(i+13) + "','" + row.get(i+17) + "','" + row.get(i+3) + "','" + row.get(i+9) + "');");
			
			// store polishID
			ResultSet rs = lastIDstmt.executeQuery("SELECT LAST_INSERT_ID()");
			rs.next();
			lastInsertID = rs.getString(1);			
			
			// create a list of orderIDs
			
			j=0;
			
			while (j < 5)
			{
				if ( ((String)(row.get(i+4+j))).equals("0") ) // if value is 0
				{
					//Do nothing					
				}
				else
					if ( isNumeric( (String)row.get(i+4+j) )) // if it's a valid orderID
					{
						listOfOrders.add(row.get(i+4+j));
					}
					else 
						if ( ((String)(row.get(i+4+j))).contains("-") ) // if it's of the XXXX-XXXX format
						{
							tempList = row.get(i+4+j);
							//sort it out
							listOfOrders.addAll(handleOrdersInPolish(tempList));
						}
						else // it's something else
						{
							//Ignore all the rest
						}
				j++;		
			}
			
		
			// insert each orderID with stored polishID into ordersInPolish
		
			ResultSet rs2;
			Statement stmt2 = conn.createStatement();
			
			// Test if OrderID is in the DB, if it is - Insert new order in polish entry
			for (int k =0; k<listOfOrders.size();k++)
			{
				rs2 = stmt2.executeQuery("SELECT COUNT(*) FROM orders WHERE orderID = '" + listOfOrders.get(k) + "';");
				rs2.next();
				System.out.println("Got : " + rs2.getString(1));
				if (rs2.getString(1).equals("1"))
				{
					System.out.println("Row: " + ((i/20)+1) + " Commiting: orderID count = " + k + " LastInsertID() = " + lastInsertID + " OrderID = " + listOfOrders.get(k));
					stmt.executeUpdate("INSERT IGNORE INTO ordersinpolish(orderID,polishID,beginDate,repolishFlag) VALUES('" + listOfOrders.get(k) + "','" + lastInsertID +"','" + row.get(i+10) + "','" + row.get(i+12) + "');");
				}
				
			}
			
			listOfOrders.clear();
		

			i=i+20;

		}
		
		stmt.close();
	

	}
	
	/**
	 * This method handles a case of several orderIDs listed in a format such as "XXXX-XXXX"
	 * and breaks it down to individual elements.
	 * 
	 * for example: "12345-12347" or "12345-47"
	 * would be broken down to 12345, 12346 and 12347.
	 */
		
	public static ArrayList<Object> handleOrdersInPolish (Object orders)
	{
		ArrayList<Object> listOfOrdersInPolish = new ArrayList<Object>();
		// remove spaces from the string		
		String str = ((String)orders).replace(" ", "");

		// find the position of "-" in the string
		int index = str.indexOf("-");
				
		// parse the two parts divided by "-" into Integers
		String left = str.substring(0, index-1);
		int leftInt = Integer.parseInt(left);
				
		String right = str.substring(index+1);
		int rightInt = Integer.parseInt(left);
		// if: both parts are Integers, continue.  <-- this is to filter out a case where "-" is in the string but the value is something other than the "XXXX-XXXX" format
		// else: return an empty list.
		
		if ( isNumeric(left) && isNumeric(right) )
		{
			int difference;
			difference = (rightInt - leftInt);
			// if condition: if Right side - Left side < 0 then it's a format similar to 12345-47
			// else: it's a format similar to 12345-12347
			
			if (difference < 0)
			{
				int howMuchToModulu;
				int relevantDigits;
	
				howMuchToModulu = 10 * right.length();
				relevantDigits = leftInt%howMuchToModulu;
				
				difference = rightInt - relevantDigits; // fix the difference to the true positive difference
				
			}
			
			while (difference >=0) 
			{
				listOfOrdersInPolish.add( String.valueOf(leftInt+difference) );
				difference--;
			}
			
		}
		else // Do nothing, listOfOrdersInPolish is empty
		{}
	
		return listOfOrdersInPolish;
		
	}
	
	public static boolean isNumeric(String str)
	{
	  NumberFormat formatter = NumberFormat.getInstance();
	  ParsePosition pos = new ParsePosition(0);
	  formatter.parse(str, pos);
	  return str.length() == pos.getIndex();
	}

}
